package com.User;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.Public.JDBConnection;


public class DBconnect extends JDBConnection {
	
	public static int DBUserRegister(UserFile RF){		//注册信息的数据库操作
		int row = 0;
		JDBConnection con=new JDBConnection();
		String sql="insert into yx_user(studentNum,userName,TrueName,password,qq,longNum,shortNum," +
					"address,college,major,year,email,superUser,regTime) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = con.CreatePreparedStatement(sql);
		try{	//写入信息
			ps.setString(1,RF.getStudentNum());
			ps.setString(2,RF.getUserName());
			ps.setString(3,RF.getTrueName());
			ps.setString(4,RF.getPassword());
			ps.setString(5,RF.getQq());
			ps.setString(6,RF.getLongNum());
			ps.setString(7,RF.getShortNum());
			ps.setString(8,RF.getAddress());
			ps.setString(9,RF.getCollege());
			ps.setString(10,RF.getMajor());
			ps.setInt(11,RF.getYear());
			ps.setString(12, RF.getEmail());
			ps.setInt(13, 0);
			ps.setLong(14, RF.getRegTime());
			row = ps.executeUpdate();
			if(row > 0)
				System.out.println("成功添加了" + row + "条数据");
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				ps.close();
			} catch (SQLException e) {
			e.printStackTrace();
			}
		}
		con.closeConnection();
		return row;
	}
	
	public static void DBUserLogin(LoginInformation LI){	//登陆数据库读取，验证账户信息
		JDBConnection con = new JDBConnection();
		String sql = null;
		if(LI.type.equals("name"))
			sql = "SELECT * FROM yx_user WHERE userName='" + LI.userName + "'";
		else if (LI.type.equals("number"))
			sql = "SELECT * FROM yx_user WHERE studentNum='" + LI.studentNum + "'";
		else if (LI.type.equals("email"))
			sql = "SELECT * FROM yx_user WHERE email='" + LI.email + "'";
		ResultSet rs = con.executeQuery(sql);
		try{
			if(rs.next()){
				LI.id =  rs.getInt("userID");
				LI.userName = rs.getString("userName");
				LI.password = rs.getString("password");
				LI.TrueName = rs.getString("TrueName");
				LI.studentNum = rs.getString("studentNum");
				LI.superUser = rs.getInt("superUser");
			}
			rs.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		con.closeConnection();
	}
	
	public static int DocItemAddItem(DocItem DI){
		int row = 0;
		JDBConnection con=new JDBConnection();
		String sql="insert into yx_docitem(itemType,printType,userID,adminID,studentNum,TrueName,paperType," +
					"copies,isColor,isSingle,ppts,otherRequest,itemStatus,docFile,subTime,takeTime,money)" +
					" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = con.CreatePreparedStatement(sql);
		try{	//获取信息
			ps.setInt(1,DI.getitemType());
			ps.setInt(2,DI.getprintType());
			ps.setInt(3,DI.getuserID());
			ps.setInt(4,DI.getadminID());
			ps.setString(5,DI.getstudentNum());
			ps.setString(6,DI.getTrueName());
			ps.setString(7,DI.getpaperType());
			ps.setInt(8,DI.getcopies());
			ps.setInt(9,DI.getisColor());
			ps.setInt(10,DI.getisSingle());
			ps.setInt(11,DI.getppts());
			ps.setString(12,DI.getotherRequest());
			ps.setInt(13,DI.getitemStatus());
			ps.setString(14,DI.getdocFile());
			ps.setLong(15,DI.getSubTime());
			ps.setLong(16,DI.getTakeTime());
			ps.setFloat(17,DI.getmoney());
			row = ps.executeUpdate();
			if(row > 0)
				System.out.println("成功添加了" + row + "条数据");
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				ps.close();
			} catch (SQLException e) {
			e.printStackTrace();
			}
		}
		con.closeConnection();
		return row;
	}
	
	public static UserFile GetUserFile(int ID){
		JDBConnection con = new JDBConnection();
		String sql = null;
		UserFile UF = null;
		sql = "SELECT * FROM yx_user WHERE userID='" + ((Integer)ID).toString() + "'";
		ResultSet rs = con.executeQuery(sql);
		try{
			if(rs.next()){
				UF = new UserFile();
				UF.setStudentNum(rs.getString("studentNum"));
				UF.setUserName(rs.getString("userName"));
				UF.setTrueName(rs.getString("TrueName"));
				UF.setQq(rs.getString("qq"));
				UF.setLongNum(rs.getString("longNum"));
				UF.setShortNum(rs.getString("shortNum"));
				UF.setAddress(rs.getString("adress"));
				UF.setCollege(rs.getString("college"));
				UF.setMajor(rs.getString("major"));
				UF.setYear(rs.getInt("year"));
				UF.setEmail(rs.getString("email"));
				UF.setSuperUser(rs.getInt("superUser"));
				UF.setRegTime(rs.getLong("regTime"));
			}
			rs.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		con.closeConnection();
		return UF;
	}
	
	public static List<DocItem> GetDocItem(int userID,int page){
		JDBConnection con = new JDBConnection();
		Integer pagestart = (page - 1) * 5;
		String sql = "select * from yx_docitem WHERE userID = "+ ((Integer)userID).toString() + " order by subTime desc limit " + pagestart.toString() + ", 5";
		ResultSet rs = con.executeQuery(sql);
		List <DocItem> L = new ArrayList<DocItem>();
		try{
			while(rs.next()){
				DocItem DI = new DocItem();
				DI.setitemID(rs.getInt("itemID"));
				DI.setitemType(rs.getInt("itemType"));
				DI.setprintType(rs.getInt("printType"));
				DI.setuserID(rs.getInt("userID"));
				DI.setadminID(rs.getInt("adminID"));
				DI.setstudentNum(rs.getString("studentNum"));
				DI.setTrueName(rs.getString("TrueName"));
				DI.setpaperType(rs.getString("paperType"));
				DI.setcopies(rs.getInt("copies"));
				DI.setisColor(rs.getInt("isColor"));
				DI.setisSingle(rs.getInt("isSingle"));
				DI.setppts(rs.getInt("ppts"));
				DI.setotherRequest(rs.getString("otherRequest"));
				DI.setitemStatus(rs.getInt("itemStatus"));
				DI.setdocFile(rs.getString("docFile"));
				DI.setSubTime(rs.getLong("SubTime")); 
				DI.setTakeTime(rs.getLong("takeTime"));
				DI.setmoney(rs.getFloat("money"));
				L.add(DI);
			}
			rs.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		con.closeConnection();
		return L;
	}
	
	
	public static int getCount(int userID) {
		JDBConnection con = new JDBConnection();
		String sql = "select count(*) from yx_docitem where userID = " + ((Integer)userID).toString();
		ResultSet rs = con.executeQuery(sql);
		int temp = 0;
		try {
			rs.next();
			temp = rs.getInt(1);
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		con.closeConnection();
		return temp;
	}
}

